----------------------------------- UNG DUNG CONG NGHE-------------------

ALTER PROCEDURE spDonVi_GetAllAvailable
@Lang varchar(50),
@PortalID int
AS
SELECT * FROM CMS_DonVi
WHERE Lang = @Lang AND TrangThai = 1 AND PortalID = @PortalID
ORDER BY ThuTu ASC, CreateDate DESC

GO

ALTER PROCEDURE spDonVi_GetAll
@Lang varchar(50),
@PortalID int
AS
SELECT * FROM CMS_DonVi
WHERE Lang = @Lang and PortalID = @PortalID
ORDER BY ThuTu ASC, CreateDate DESC

GO

ALTER PROCEDURE spDonVi_GetAllByFilter
@Lang varchar(50),
@Filter bit,
@PortalID int
AS
BEGIN
	SELECT * FROM CMS_DonVi
	WHERE Lang = @Lang and TrangThai = @Filter and PortalID = @PortalID
	ORDER BY ThuTu ASC, CreateDate DESC
END
GO

ALTER PROCEDURE spDonVi_GetInfo
@DonViID int
AS
SELECT * FROM dbo.CMS_DonVi
WHERE DonViID = @DonViID

GO
ALTER PROCEDURE dbo.spDonVi_SetIndex
	@DonViID	int,
	@Index 	int
AS
UPDATE dbo.CMS_DonVi SET ThuTu = @Index  WHERE DonViID = @DonViID

GO

ALTER PROCEDURE dbo.spDonVi_SetStatus
	@DonViID	int,
	@Status 	int
AS
UPDATE dbo.CMS_DonVi SET TrangThai = @Status  WHERE DonViID = @DonViID

GO

ALTER PROCEDURE [dbo].[spDonVi_Insert]
(
	@CapChaID int,
	@TenDonVi nvarchar(250),	
	@NoiDung ntext,
	@TrangThai bit,
	@ThuTu int,	
	@CreateUserID int,
	@PortalID int,
	@Lang varchar(50)
)
AS
INSERT INTO CMS_DonVi
(
	CapChaID,
	TenDonVi,
	NoiDung,
	TrangThai,
	ThuTu,
	CreateDate,
	CreateUserID,
	PortalID,
	Lang
)
VALUES 
(
	@CapChaID,
	@TenDonVi,
	@NoiDung,
	@TrangThai,
	@ThuTu,
	GETDATE(),
	@CreateUserID,
	@PortalID,
	@Lang

)
DECLARE @RETURN_ID INT
SET @RETURN_ID = SCOPE_IDENTITY()
SELECT @RETURN_ID

GO

ALTER PROCEDURE [dbo].[spDonVi_Update]
(
	@DonViID int,
	@CapChaID int,
	@TenDonVi nvarchar(250),	
	@NoiDung ntext,
	@TrangThai bit,
	@ThuTu int,	
	@ModifyUserID int,
	@PortalID int,
	@Lang varchar(50)	
)
AS
UPDATE dbo.CMS_DonVi SET
	CapChaID = @CapChaID,
	TenDonVi = @TenDonVi,
	NoiDung = @NoiDung,
	TrangThai = @TrangThai,
	ThuTu = @ThuTu,
	ModifyDate = GETDATE(),
	ModifyUserID = @ModifyUserID,
	PortalID = @PortalID,
	Lang = @Lang	

WHERE DonViID = @DonViID

GO

ALTER PROCEDURE [dbo].spDonVi_Delete
@DonViID int
AS
BEGIN
	SET NOCOUNT ON;

delete dbo.CMS_DonVi
 where DonViID = @DonViID

END

GO

ALTER PROCEDURE spDonVi_Count
@Lang varchar(50),
@PortalID int	
AS
BEGIN
	SELECT COUNT(*) AS 'COUNT' FROM CMS_DonVi WHERE Lang = @Lang and PortalID = @PortalID
END

GO

ALTER PROCEDURE sp_DonViParentChildren
@id int,
@Lang varchar(50),
@PortalID int
as
begin
with x
	as
	(
	   select
		  DonViID, CapChaID, TenDonVi, Lang, ThuTu, PortalID
	   from
		  CMS_DonVi
	   where
		  DonViID = @ID
	   union all
	   select
		  m.DonViID, m.CapChaID, m.TenDonVi, m.Lang, m.ThuTu, m.PortalID
	   from
		  CMS_DonVi m
	   join
		  x on x.DonViID = m.CapChaID
	)
	select *
	from x
	where x.Lang = @Lang and x.PortalID = PortalID
	order by ThuTu asc
end

GO

--CREATE PROC spDonViChildrenByParent
--@CapChaID int,
--@Lang varchar(50)
--AS
--BEGIN
--	SELECT * FROM CMS_DonVi
--	WHERE CapChaID = @CapChaID and Lang = @Lang
--	ORDER BY THUTU
--END

ALTER PROCEDURE spGetDonViByParentIDAndPortalID
(
@ParentID int,
@PortalID int,
@Lang varchar(50)
)
AS
begin
	SELECT     dbo.CMS_DonVi.*
	FROM       dbo.CMS_DonVi
	WHERE CapChaID = @ParentID And Lang = @Lang And PortalID = @PortalID
	ORDER BY ThuTu
end

GO
-----------------------------------END UNG DUNG CONG NGHE-----------------------------